﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FU.Capstones.IMS.Services.Common;
using FU.Capstones.IMS.Services.Common.DataContracts;
using FU.Capstones.IMS.Services.Common.Respondents;
using FU.Capstones.IMS.Services.DataContracts;
using FU.Capstones.IMS.Services.Interfaces;

namespace FU.Capstones.IMS.Services.DAOs
{
    public class EmployeeDAO : DAOBase, IEmployeeDAO
    {
        #region IEmployeeDAO Members

        public CollectionFeedback<Employee> GetCollection(bool isShowAll)
        {
            var feedback = new CollectionFeedback<Employee> {IsSuccessful = false, Data = new List<Employee>()};

            try
            {
                OpenConnection();

                var command = new SqlCommand("GetCollectionEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                SqlDataReader reader = command.ExecuteReader();

                var employees = new List<Employee>();

                while (reader.Read())
                {
                    var employmentType = new EmploymentType();
                    employmentType.EmploymentTypeName = Convert.ToString(reader["EmploymentType"]);

                    var role = new Role();
                    role.RoleName = Convert.ToString(reader["RoleName"]);

                    var employee = new Employee();
                    employee.EmploymentType = employmentType;
                    employee.Role = role;
                    employee.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
                    employee.FullName = Convert.ToString(reader["FullName"]);
                    employee.JoinedDate = Convert.ToDateTime(reader["JoinedDate"]);
                    employee.Address = Convert.ToString(reader["Address"]);
                    employee.Description = Convert.ToString(reader["Description"]);
                    employee.Username = Convert.ToString(reader["Username"]);
                    employee.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    employee.PhoneNumber = Convert.ToString(reader["PhoneNumber"]);
                    employee.MobileNumber = Convert.ToString(reader["MobileNumber"]);
                    employee.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    employee.IdentityCardNumber = Convert.ToString(reader["IdentityCardNumber"]);
                    employee.Gender = Convert.ToString(reader["Gender"]);
                    employee.Email = Convert.ToString(reader["Email"]);
                    employee.Username = Convert.ToString(reader["Username"]);

                    employees.Add(employee);
                }
                feedback.Message = InventoryConstants.Messages.GetSuccessfully;
                feedback.IsSuccessful = true;
                feedback.Data = employees;

                //Log.Info("GetCollectionEmployee: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                //Log.Error("GetCollectionEmployee - SqlException: {0}, {1}", e.StackTrace, DateTime.Now.ToString());
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            catch (Exception t)
            {
                //Log.Error("GetCollectionEmployee - Exception: {0}, {1}", t.StackTrace, DateTime.Now.ToString());
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public CollectionFeedback<Employee> GetCollectionType(bool isShowAll)
        {
            var feedback = new CollectionFeedback<Employee> {IsSuccessful = false, Data = new List<Employee>()};

            try
            {
                OpenConnection();

                var command = new SqlCommand("GetCollectionEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                SqlDataReader reader = command.ExecuteReader();

                var employees = new List<Employee>();

                while (reader.Read())
                {
                    var employmentType = new EmploymentType();
                    employmentType.EmploymentTypeName = Convert.ToString(reader["EmploymentType"]);

                    var role = new Role();
                    role.RoleName = Convert.ToString(reader["RoleName"]);

                    var employee = new Employee();
                    employee.EmploymentType = employmentType;
                    employee.Role = role;
                    employee.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
                    employee.FullName = Convert.ToString(reader["FullName"]);
                    employee.JoinedDate = Convert.ToDateTime(reader["JoinedDate"]);
                    employee.Address = Convert.ToString(reader["Address"]);
                    employee.Description = Convert.ToString(reader["Description"]);
                    employee.Username = Convert.ToString(reader["Username"]);
                    employee.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    employee.PhoneNumber = Convert.ToString(reader["PhoneNumber"]);
                    employee.MobileNumber = Convert.ToString(reader["MobileNumber"]);
                    employee.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    employee.IdentityCardNumber = Convert.ToString(reader["IdentityCardNumber"]);
                    employee.Gender = Convert.ToString(reader["Gender"]);
                    employee.Email = Convert.ToString(reader["Email"]);
                    employee.Username = Convert.ToString(reader["Username"]);

                    employees.Add(employee);
                }
                feedback.Message = InventoryConstants.Messages.GetSuccessfully;
                feedback.IsSuccessful = true;
                feedback.Data = employees;

                //Log.Info("GetCollectionEmployee: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                //Log.Error("GetCollectionEmployee - SqlException: {0}, {1}", e.StackTrace, DateTime.Now.ToString());
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            catch (Exception t)
            {
                //Log.Error("GetCollectionEmployee - Exception: {0}, {1}", t.StackTrace, DateTime.Now.ToString());
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public SingleFeedback<Employee> GetSingle(int objectID)
        {
            var feedback = new SingleFeedback<Employee>
                               {
                                   IsSuccessful = false,
                                   Data = new Employee
                                              {
                                                  Role = new Role(),
                                                  EmploymentType = new EmploymentType(),
                                                  AuthorizationCollection = new List<int>()
                                              }
                               };

            try
            {
                OpenConnection();

                var command = new SqlCommand("GetSingleEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@EmployeeID", objectID);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows && reader.Read())
                {
                    feedback.Data.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
                    feedback.Data.FullName = Convert.ToString(reader["FullName"]);
                    feedback.Data.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    feedback.Data.IdentityCardNumber = Convert.ToString(reader["IdentityCardNumber"]);
                    feedback.Data.Gender = Convert.ToString(reader["Gender"]);
                    feedback.Data.JoinedDate = Convert.ToDateTime(reader["JoinedDate"]);
                    feedback.Data.Email = Convert.ToString(reader["Email"]);
                    feedback.Data.Address = Convert.ToString(reader["Address"]);
                    feedback.Data.PhoneNumber = Convert.ToString(reader["PhoneNumber"]);
                    feedback.Data.MobileNumber = Convert.ToString(reader["MobileNumber"]);
                    feedback.Data.Description = Convert.ToString(reader["Description"]);
                    feedback.Data.Username = Convert.ToString(reader["Username"]);
                    feedback.Data.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    feedback.Data.Role.RoleID = Convert.ToInt32(reader["RoleID"]);
                    feedback.Data.Role.RoleName = Convert.ToString(reader["RoleName"]);

                    feedback.Data.EmploymentType.EmploymentTypeID = Convert.ToInt32(reader["EmploymentTypeID"]);
                    feedback.Data.EmploymentType.EmploymentTypeName = Convert.ToString(reader["EmploymentType"]);

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            feedback.Data.AuthorizationCollection.Add(Convert.ToInt32(reader["AuthorizationID"]));
                        }
                    }

                    feedback.Message = InventoryConstants.Messages.GetSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.NA;
                }

                //Log.Info("GetSingleEmployee: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                //Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            catch (Exception t)
            {
                //Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.GetFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public SingleFeedback<int> Insert(Employee data)
        {
            var feedback = new SingleFeedback<int> {IsSuccessful = false, Data = -1};
            try
            {
                OpenConnection();

                var command = new SqlCommand("InsertEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@FullName", data.FullName);
                command.Parameters.AddWithValue("@DateOfBirth", data.DateOfBirth);
                command.Parameters.AddWithValue("@IdentityCardNumber", data.IdentityCardNumber);
                command.Parameters.AddWithValue("@Gender", data.Gender);
                command.Parameters.AddWithValue("@JoinedDate", data.JoinedDate);
                command.Parameters.AddWithValue("@Email", data.Email);
                command.Parameters.AddWithValue("@Address", data.Address);
                command.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
                command.Parameters.AddWithValue("@MobileNumber", data.MobileNumber);
                command.Parameters.AddWithValue("@Description", data.Description);
                command.Parameters.AddWithValue("@EmploymentTypeID", data.EmploymentType.EmploymentTypeID);
                command.Parameters.AddWithValue("@RoleID", data.Role.RoleID);
                command.Parameters.AddWithValue("@Username", data.Username);
                command.Parameters.AddWithValue("@AuthorizationIDList", GetIDString(data.AuthorizationCollection));

                command.Parameters.Add("@EmployeeID", SqlDbType.Int);
                command.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

                command.ExecuteNonQuery();

                int temp = Convert.ToInt32(command.Parameters["@EmployeeID"].Value);
                if (temp > 0)
                {
                    feedback.Message = InventoryConstants.Messages.InsertSuccessfully;
                    feedback.IsSuccessful = true;
                    feedback.Data = temp;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.InsertFailed;
                }

                Log.Info("InsertEmployee: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.InsertFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.InsertFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Update(Employee data)
        {
            var feedback = new Feedback {IsSuccessful = false};
            try
            {
                OpenConnection();

                var command = new SqlCommand("UpdateEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@EmployeeID", data.EmployeeID);
                command.Parameters.AddWithValue("@FullName", data.FullName);
                command.Parameters.AddWithValue("@DateOfBirth", data.DateOfBirth);
                command.Parameters.AddWithValue("@IdentityCardNumber", data.IdentityCardNumber);
                command.Parameters.AddWithValue("@Gender", data.Gender);
                command.Parameters.AddWithValue("@JoinedDate", data.JoinedDate);
                command.Parameters.AddWithValue("@Email", data.Email);
                command.Parameters.AddWithValue("@Address", data.Address);
                command.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
                command.Parameters.AddWithValue("@MobileNumber", data.MobileNumber);
                command.Parameters.AddWithValue("@Description", data.Description);
                command.Parameters.AddWithValue("@EmploymentTypeID", data.EmploymentType.EmploymentTypeID);
                command.Parameters.AddWithValue("@RoleID", data.Role.RoleID);
                command.Parameters.AddWithValue("@Username", data.Username);
                command.Parameters.AddWithValue("@AuthorizationIDList", GetIDString(data.AuthorizationCollection));
                //
                command.Parameters.Add(new SqlParameter
                                           {
                                               Direction = ParameterDirection.Output,
                                               ParameterName = "@Result",
                                               SqlDbType = SqlDbType.Bit
                                           });

                command.ExecuteNonQuery();
                bool result = Convert.ToBoolean(command.Parameters["@Result"].Value);
                if (!result)
                {
                    feedback.Message = InventoryConstants.Messages.UpdateFailed;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.UpdateSuccessfully;
                    feedback.IsSuccessful = true;
                }

                Log.Info("UpdateEmployee: {0}, {1}, {2}", data.EmployeeID, feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.UpdateFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.UpdateFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Delete(int objID)
        {
            var feedback = new Feedback {IsSuccessful = false};

            try
            {
                OpenConnection();

                var command = new SqlCommand("DeleteEmployee", imsConnection);
                var command2 = new SqlCommand("Select * From [Order]", imsConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@EmployeeID", objID);

                var da = new SqlDataAdapter(command2);
                var dt = new DataTable("OrderInformation");
                da.Fill(dt);

                int i = 0;
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow dr2 = dt.Rows[i];
                        if (objID == Convert.ToInt32(dr2["EmployeeAID"]) || objID == Convert.ToInt32(dr2["EmployeeBID"]))
                        {
                            i++;
                        }
                    }
                }

                if (i == 0)
                {
                    int rowAffect = command.ExecuteNonQuery();

                    if (rowAffect >= 1)
                    {
                        feedback.Message = InventoryConstants.Messages.DeleteSuccessfully;
                        feedback.IsSuccessful = true;
                    }
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.DeleteFailed;
                }

                Log.Info("DeleteEmployee: {0}, {1}, {2}", objID, feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.DeleteFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.DeleteFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public CollectionFeedback<Employee> Search(Employee criteria)
        {
            var feedback = new CollectionFeedback<Employee> {IsSuccessful = false, Data = new List<Employee>()};
            var eCriteria = (EmployeeCriteria) criteria;

            try
            {
                OpenConnection();

                var command = new SqlCommand("SearchEmployee", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@FullName",
                                                String.IsNullOrEmpty(eCriteria.FullName)
                                                    ? eCriteria.FullName
                                                    : eCriteria.FullName.Trim());
                command.Parameters.AddWithValue("@DateOfBirthFrom", eCriteria.DateOfBirthFrom);
                command.Parameters.AddWithValue("@DateOfBirthTo", eCriteria.DateOfBirthTo);
                command.Parameters.AddWithValue("@JoinedDateFrom", eCriteria.JoinedDateFrom);
                command.Parameters.AddWithValue("@JoinedDateTo", eCriteria.JoinedDateTo);
                if (eCriteria.RoleIDCollection != null && eCriteria.RoleIDCollection.Count > 0)
                {
                    command.Parameters.AddWithValue("@RoleID", eCriteria.RoleIDCollection[0]);
                }


                SqlDataReader reader = command.ExecuteReader();

                var employees = new List<Employee>();

                while (reader.Read())
                {
                    var employmentType = new EmploymentType();
                    employmentType.EmploymentTypeID = Convert.ToInt32(reader["EmploymentTypeID"]);
                    employmentType.EmploymentTypeName = Convert.ToString(reader["EmploymentType"]);

                    var role = new Role();
                    role.RoleID = Convert.ToInt32(reader["RoleID"]);
                    role.RoleName = Convert.ToString(reader["RoleName"]);

                    var employee = new Employee();
                    employee.EmploymentType = employmentType;
                    employee.Role = role;
                    employee.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
                    employee.FullName = Convert.ToString(reader["FullName"]);
                    employee.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    employee.IdentityCardNumber = Convert.ToString(reader["IdentityCardNumber"]);
                    employee.Gender = Convert.ToString(reader["Gender"]);
                    employee.JoinedDate = Convert.ToDateTime(reader["JoinedDate"]);
                    employee.Email = Convert.ToString(reader["Email"]);
                    employee.Address = Convert.ToString(reader["Address"]);
                    employee.PhoneNumber = Convert.ToString(reader["PhoneNumber"]);
                    employee.MobileNumber = Convert.ToString(reader["MobileNumber"]);
                    employee.Description = Convert.ToString(reader["Description"]);
                    employee.Username = Convert.ToString(reader["Username"]);
                    employee.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    employees.Add(employee);
                }
                feedback.Message = InventoryConstants.Messages.SearchSuccessfully;
                feedback.IsSuccessful = true;
                feedback.Data = employees;


                Log.Info("SearchEmployee: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback UndoDelete(int objID)
        {
            var feedback = new Feedback {IsSuccessful = false};

            try
            {
                OpenConnection();

                var command = new SqlCommand("UndoDeletedEmployee", imsConnection);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@EmployeeID", objID);

                int rowAffect = command.ExecuteNonQuery();

                if (rowAffect >= 1)
                {
                    feedback.Message = InventoryConstants.Messages.UndoDeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }

                Log.Info("UndoDeletedEmployee: {0}, {1}, {2}", objID, feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.UndoDeleteFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.UndoDeleteFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        #endregion
    }
}